package com.ccsc.auth.utils;

import com.ccsc.auth.entity.ShiroResource;
import com.ccsc.auth.entity.ShiroRole;
import com.ccsc.auth.entity.ShiroSystem;
import com.ccsc.auth.entity.ShiroUser;
import com.google.common.collect.Lists;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

/**
 * Created by XingWen on 2017/3/3.
 */
public class SSOResourceUtil {

    /**
     * @param args
     */
    //驱动程序就是之前在classpath中配置的JDBC的驱动程序的JAR 包中
    public static final String DBDRIVER = "com.mysql.jdbc.Driver";
    //连接地址是由各个数据库生产商单独提供的，所以需要单独记住
    public static final String DBURL = "jdbc:mysql://192.168.112.21:3306/kmga?useUnicode=true&characterEncoding=UTF8&useSSL=false";
    //连接数据库的用户名
    public static final String DBUSER = "open";
    //连接数据库的密码
    public static final String DBPASS = "123456";

    private static final String SQL_GET_USER_BY_USER_ID = "select * from sdp_kmga_sys_user where user_id = ? and user_status = 1";
    private static final String SQL_GET_ROLE_BY_USERID = "select b.* from sdp_kmga_sys_rel_authorize a LEFT join sdp_kmga_sys_role b on a.role_id=b.role_id where a.user_id = ? ";
    private static final String SQL_GET_RESOURCE_BY_ROLE_ID = "select b.* from sdp_kmga_sys_rel_permission a left join sdp_kmga_sys_resource b on a.resource_id=b.resource_id left join sdp_kmga_sys_system c on b.system_id=c.system_id where a.role_id in ";
    private static final String SQL_GET_SYSTEMS = "select * from sdp_kmga_sys_system where system_status = 1 order by system_id";

    //通过系统用户ID与账号查询数据监管平台用户id
    private static final String SQL_GET_USER_BY_SYS_USER_ID = "select * from sdp_kmga_sys_user where sys_user_id = ?";


    public static ShiroUser getUserById(String userId){
        Connection con = null; //表示数据库的连接对象
        PreparedStatement stmt = null;  //表示数据库的更新操作
        ResultSet result = null; //表示接收数据库的查询结果
        try {
            Class.forName(DBDRIVER); //1、使用CLASS 类加载驱动程序
            con = DriverManager.getConnection(DBURL, DBUSER, DBPASS); //2、连接数据库
            ShiroUser shiroUser = new ShiroUser();
            //添加角色
            //stmt = con.prepareStatement(SQL_GET_USER_BY_USER_ID); //3、Statement 接口需要通过Connection 接口进行实例化操作
            stmt = con.prepareStatement(SQL_GET_USER_BY_SYS_USER_ID);
            stmt.setString(1,userId);
            //先通过userid拿到user的基本信息 然后拿到角色列表 然后通过角色列表拿到 所有的资源
            result = stmt.executeQuery(); //执行SQL 语句，查询数据库
            if (result.next()) {
                shiroUser.setUserId(result.getInt("user_id"));
                shiroUser.setUserName(result.getString("user_name"));
                shiroUser.setAccount(result.getString("user_account"));
                shiroUser.setMail(result.getString("user_mail"));
                shiroUser.setPhone(result.getString("user_phone"));
                shiroUser.setSex(result.getInt("user_sex"));

                //在这里面 将角色列表加入其中
                PreparedStatement stmt1 = null;
                ResultSet result1 = null;
                try{
                    stmt1 = con.prepareStatement(SQL_GET_ROLE_BY_USERID);
                    stmt1.setString(1,String.valueOf(shiroUser.getUserId()));
                    result1 = stmt1.executeQuery();
                    List<ShiroRole> shiroRoles = Lists.newArrayList();
                    String roleStr = "";
                    while(result1.next()){
                        ShiroRole shiroRole = new ShiroRole();
                        shiroRole.setRoleId(result1.getInt("role_id"));
                        roleStr += "," + shiroRole.getRoleId();
                        shiroRole.setRoleName(result1.getString("role_name"));
                        shiroRole.setRoleDescription(result1.getString("role_description"));
                        shiroRoles.add(shiroRole);
                    }
                    shiroUser.setRoles(shiroRoles);

                    List<ShiroSystem> systems = Lists.newArrayList();
                    //得到系统列表
                    PreparedStatement stmt2 = null;
                    ResultSet result2 = null;
                    try{
                        stmt2 = con.prepareStatement(SQL_GET_SYSTEMS);
                        result2 = stmt2.executeQuery();
                        while(result2.next()){
                            ShiroSystem shiroSystem = new ShiroSystem();
                            shiroSystem.setSystemId(result2.getInt("system_id"));
                            shiroSystem.setSystemName(result2.getString("system_name"));
                            shiroSystem.setSortSeq(result2.getInt("sort_seq"));
                            shiroSystem.setSystemDescription(result2.getString("system_description"));
                            shiroSystem.setSystemIdentifier(result2.getString("system_identifier"));
                            shiroSystem.setSystemUrl(result2.getString("system_url"));
                            systems.add(shiroSystem);
                        }
                    }catch (Exception e){
                        e.printStackTrace();
                    }finally {
                        result2.close();
                        stmt2.close();
                    }
                    if(roleStr.length()>1) {
                        List<ShiroResource> shiroResources = Lists.newArrayList();
                        //获得改角色所有资源
                        PreparedStatement stmt3 = null;
                        ResultSet result3 = null;
                        try {
                            stmt3 = con.prepareStatement(SQL_GET_RESOURCE_BY_ROLE_ID +"("  + roleStr.substring(1) + ")" + "order by -sort desc,b.resource_id");
                            //stmt3 = con.prepareStatement(SQL_GET_RESOURCE_BY_ROLE_ID +"("  + roleStr.substring(1) + ")");
                            //stmt3.setString(1, "(" + roleStr.substring(1) + ")");
                            result3 = stmt3.executeQuery();
                            while (result3.next()) {
                                ShiroResource shiroResource = new ShiroResource();
                                shiroResource.setResourceId(result3.getInt("resource_id"));
                                shiroResource.setResourceContent(result3.getString("resource_content"));
                                shiroResource.setResourceDescription(result3.getString("resource_description"));
                                shiroResource.setResourceParentId(result3.getInt("resource_parentid"));
                                shiroResource.setResourceIcon(result3.getString("resource_icon"));
                                shiroResource.setResourceType(result3.getString("resource_type"));
                                for(ShiroSystem sys:systems){
                                    if(sys.getSystemId() == result3.getInt("system_id")){
                                        if(sys.getResources()==null) {
                                            List<ShiroResource> resources = Lists.newArrayList();
                                            sys.setResources(resources);
                                        }
                                        if(!sys.getResources().contains(shiroResource)) {
                                            sys.getResources().add(shiroResource);
                                        }
                                    }
                                }
                            }
                            shiroUser.setSystems(systems);
                        } catch (Exception e) {
                            e.printStackTrace();
                        } finally {
                            result3.close();
                            stmt3.close();
                        }
                    }

                }catch (Exception e){
                    e.printStackTrace();
                }finally {
                    result1.close();
                    stmt1.close();
                }

            }
            return shiroUser;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try {
                result.close();
                stmt.close();
                con.close(); // 3、关闭数据库
            }
            catch (Exception ex){

            }
        }
    }
}
